package com.lcq.study.transsql;

import java.io.*;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @ClassName TransSql
 * @Description  powerDesigner脚本转换程序
 * @Author lichuanqi
 * @Date 2022/1/6 11:28
 * @Version 1.0
 **/
public class TransSql {

    public static void main(String[] args) throws IOException {
        File srcFile = new File("E:/test/sql/ckp.txt");
        BufferedReader bufferedReader = new BufferedReader(new FileReader(srcFile));
        List<String> lines = bufferedReader.lines().collect(Collectors.toList());

        String destName = "sql"+System.currentTimeMillis()+".txt";
        File destFile = new File(  "E:/test/sql/"+destName);
        BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(destFile));
        boolean read = false;
        boolean write = false;
        boolean header = false;
        StringBuilder stringBuilder = new StringBuilder();
        System.out.println(lines.size());
        for (String line:lines){
            if (line.startsWith("if") || line.startsWith("create table")){
                read = true;
                write = false;
            }

            if (line.startsWith("go")){
                write = true;
                read = false;
            }

            if (read) {
                stringBuilder.append(line.trim()+"\r\n");
            }

            if (write) {
                String outStr = stringBuilder.append(";").toString();
                if (outStr.contains("sysobjects") && header == false){
                    read = true;
                    write = false;
                    header = true;
                    bufferedWriter.write("\r\n");
                    continue;
                }

                if (outStr.contains("sp_addextendedproperty")){
                    outStr = getTransProperty(outStr);
                }
                outStr += "\r\n";
                bufferedWriter.write(outStr);
                //System.out.println(outStr);
                stringBuilder.delete(0, stringBuilder.length());

                if (header == true){
                    header = false;
                }

                if (write == true){
                    write = false;
                }
            }

        }
        bufferedReader.close();
        bufferedWriter.close();
    }

    private static String getTransProperty(String outStr) {
        outStr = outStr.substring(outStr.indexOf("sp_addextendedproperty"));
        String[] split = outStr.split(",");
        String desc = "";
        String table = "";
        String column = "";
        String template = "EXEC sp_addextendedproperty N'MS_Description', N'%s', 'SCHEMA', N'dbo', 'TABLE', N'%s'";
        String columnTemp = " 'COLUMN', N'%s'";
        for (int i=0; i < split.length; i++){
            if (split[i].contains("MS_Description")){
                desc = split[i + 1].replace("'","").replace(";", "").trim();
            }

            if (split[i].contains("table")){
                table = split[i + 1].replace("'","").replace(";", "").trim();
            }

            if (split[i].contains("column")){
                column = split[i + 1].replace("'","").replace(";", "").trim();
            }
        }

        if (!desc.equals("") && !table.equals("")){
            String format1 = String.format(template, desc, table);
            if (!column.equals("")){
                String format2 = String.format(columnTemp, column);
                return format1+","+format2+";";
            }
            return format1+";";
        }
        return outStr;
    }
}
